package db;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

import core.common.Configurations;

public class DBConnection implements AutoCloseable {

    private Connection con;
    private String dbURL;
    private String username;
    private String password;
    private Statement statement;
    private PreparedStatement preparedStatement;
    private ResultSet resultSet;

    public PreparedStatement createPreparedStatement(String preSt) throws SQLException {
        if (preSt == null) return null;
        try {
            if (preparedStatement != null && !preparedStatement.isClosed()) {
                preparedStatement.close();
            }
        } catch (SQLException ex) {
            Logger.getLogger(DBConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        preparedStatement = con.prepareStatement(preSt);
        return preparedStatement;
    }

    public DBConnection(String dbURL, String username, String password) throws SQLException, ClassNotFoundException {
        this.dbURL = dbURL;
        this.username = username;
        this.password = password;
        connect();
    }

    public final void connect() throws SQLException, ClassNotFoundException {
        // setup the connection with the DB.
        con = DriverManager
                .getConnection("jdbc:mysql://" + dbURL + "/" + Configurations.getDbSchema() + "?useServerPrepStmts=false&rewriteBatchedStatements=true&"
                + "user=" + username + "&password=" + password);
    }

    @Override
    public void close() {
        closeResource(resultSet);
        closeResource(statement);
        closeResource(preparedStatement);
        closeResource(con);
    }

    private void closeResource(AutoCloseable c) {
        try {
            if (c != null) {
                c.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void createStatement() throws SQLException {
        if (statement == null || statement.isClosed()) {
            statement = con.createStatement();
        }
    }

    /**
     * Execute insert and update queries
     * @param statementStr
     * @return the number of lines that were updated
     * @throws SQLException 
     */
    public int executeUpdate(String statementStr) throws SQLException {
        createStatement();
        return statement.executeUpdate(statementStr);
    }

    /**
     * Execute select query
     * @param statementStr
     * @return the selected rows.
     * @throws SQLException 
     */
    public ResultSet executeQuery(String statementStr) throws SQLException {
        createStatement();
        return statement.executeQuery(statementStr);
    }

    public int[] executePreparedStatementBatch() throws SQLException {
        return preparedStatement.executeBatch();
    }

    public void executeSQLScript(String path) throws SQLException, IOException {
        ScriptRunner sr = new ScriptRunner(con, false, true);
        sr.runScript(DBConnection.class.getResourceAsStream(path));
    }
    
    /**
     * Sets the execution of current connection as transaction
     */
    public void beginTransaction() throws SQLException {
    	con.setAutoCommit(false);
    }
    
    /**
     * Commits the connection
     */
    public void commitTransaction() throws SQLException {
    	try {
        	con.commit();    		
    	} finally {
	    	//make connection not transaction unless specifically stated:
	    	con.setAutoCommit(true);
    	}
    }
    
    /**
     * Canceling transaction - next time this connection will be taken
     * from pool, it will not be marked as a transaction connection
     * unless beginTransaction() is called again!
     */
    public void cancelTransaction() throws SQLException {
    	con.setAutoCommit(true);
    }
}
